using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Collections;
using DTO;
namespace DAO
{
    public class TaiKhoanDAO
    {
        DataProvider Provider;
        public TaiKhoanDAO()
        {
            Provider = new DataProvider();
        }

        public DataTable TimKiemUser(string Username)
        {
            Provider.Connect();
            DataTable dt = new DataTable();
            //B3: Tao chuoi strSQL thao tac CSDL
            string str = "Select * From TAIKHOAN Where Username like '%" + Username + "%'";
            //B4: Thuc thi chuoi strSQL
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }

        public DataTable LayBangTaiKhoan()
        {
            Provider.Connect();
            string str = "select * from TAIKHOAN";
            DataTable dt = new DataTable();
            OleDbDataAdapter da;
            da = new OleDbDataAdapter(str, Provider.cn);
            da.Fill(dt);
            Provider.Disconnect();
            return dt;
        }


        public void ThemUser(TaiKhoanDTO tkDto)
        {
            Provider.Connect();
            string str;
            str = "Insert into TAIKHOAN([Password], Quyen, Username) values (?, ?, ?) ";

            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            
            cmd.Parameters.Add("@Password", OleDbType.WChar);
            cmd.Parameters.Add("@Quyen", OleDbType.WChar);
            cmd.Parameters.Add("@Username", OleDbType.WChar);
            
            cmd.Parameters["@Password"].Value = tkDto.Password;
            cmd.Parameters["@Quyen"].Value = tkDto.Quyen;
            cmd.Parameters["@Username"].Value = tkDto.Username;

            cmd.ExecuteNonQuery();
            Provider.Disconnect();

        }


        public void XoaUser(string maTK)
        {
            // B1 & B2: Tao chuoi ket noi, mo ket noi bang doi tuong ket noi
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Delete From TAIKHOAN Where Username = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);
            cmd.Parameters.Add("@Username", OleDbType.Char);
            cmd.Parameters["@Username"].Value = maTK;
            cmd.ExecuteNonQuery();
            // B5: Dong ket noi CSDL
            Provider.Disconnect();
        }

        public void SuaUser(TaiKhoanDTO tkDto)
        {
            Provider.Connect();
            // B3: Tao chuoi strSQL thao tac CSDL
            string str;
            str = "Update TaiKhoan Set [Password] = ?, Quyen = ? Where Username = ? ";
            OleDbCommand cmd = new OleDbCommand(str, Provider.cn);

            
            cmd.Parameters.Add("@Password", OleDbType.WChar);
            cmd.Parameters.Add("@Quyen", OleDbType.WChar);
            cmd.Parameters.Add("@Username", OleDbType.WChar);
            
            cmd.Parameters["@Password"].Value = tkDto.Password;
            cmd.Parameters["@Quyen"].Value = tkDto.Quyen;
            cmd.Parameters["@Username"].Value = tkDto.Username;
            

            cmd.ExecuteNonQuery();
            Provider.Disconnect();
        }

    }
}
